 SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[CaseManagement].[CasesForServiceMember]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [CaseManagement].[CasesForServiceMember]
END

GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[CasesForServiceMember]( @ServiceManID BIGINT )
AS  
BEGIN

	Select  Cases.OID as 'CaseOID', 
			Cases.Version as 'CaseVersion', 
			Cases.ServiceManID, 
			Cases.CaseVolunteerID,
			Cases.CaseDirectorID,
			Cases.InjuryDate,

			Users.OID as 'UsersOID', 
			Users.GUID, 
			Users.FullName, 
			Users.UPN, 
			Users.LastSignedIn

	From Cases
	Join Users On Users.OID = Cases.CaseVolunteerID
	Where
		Cases.ServiceManID = @ServiceManID

END 
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [CaseManagement].[CasesForServiceMember] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership    